﻿namespace UnitTest.Dxp.Orm.Dsl

open System
open Microsoft.VisualStudio.TestTools.UnitTesting
open Lib.Dxp.Core
open Lib.Dxp.Orm
open Lib.Dxp.Orm.Dsl
open Lib.Dxp.Orm.DataValueInit


module Debug =
    module SqlCommand =
        let inline asLogInfo(cmd : System.Data.SqlClient.SqlCommand) =
            let mutable result = "QRY\r\n{0}\r\n".format(cmd.CommandText)
            for p in cmd.Parameters do result <- result + "`{0}`=`{1}` ".format(p.ParameterName, p.SqlValue)
            result

        let inline asFullScript(cmd : System.Data.SqlClient.SqlCommand) =
            let mutable result = "QRY\r\n{0}\r\n".format(cmd.CommandText)
            for p in [ for p in cmd.Parameters -> p ] |> List.rev do
                result <- result.Replace(p.ParameterName, sprintf "%A" p.SqlValue)
            result



    module Code =
        let trim (txt : string) =
            let y = txt.Trim().Split('\r')
            let y = y |> Array.map (fun e -> e.Trim())
            "\r\n".join y


type Categories internal () = 
    inherit ExtTable()
    let _catname = text()
    static member ctor with get() = let x = Categories() in x.init(); x
    member  val   CategoryID = int () with get
    member  x.CategoryName = _catname 
    member  val  Description = text() with get
    member  val      Picture = text() with get // TDOD:blob

type Customers internal () = 
    inherit ExtTable()
    static member ctor with get() = let x = Customers() in x.init(); x
    member val     CustomerID = varchar 5  with get        // "Customers|CustomerID"  false  true
    member val    CompanyName = varchar 40 with get        //  "Customers|CompanyName"  false  false
    member val    ContactName = varchar 30 with get        //  "Customers|ContactName"  true  false
    member val   ContactTitle = varchar 30 with get        //  "Customers|ContactTitle"  true  false
    member val        Address = varchar 60 with get        //  "Customers|Address"  true  false
    member val           City = varchar 15 with get        //  "Customers|City"  true  false
    member val         Region = varchar 15 with get        //  "Customers|Region"  true  false
    member val     PostalCode = varchar 10 with get        //  "Customers|PostalCode"  true  false
    member val        Country = varchar 15 with get        //  "Customers|Country"  true  false
    member val          Phone = varchar 24 with get        //  "Customers|Phone"  true  false
    member val            Fax = varchar 24 with get        //  "Customers|Fax"  true  false

type Orders internal () = 
    inherit ExtTable()
    static member ctor with get() = let x = Orders() in x.init(); x
    member val        OrderID = int   ()   with get     //(10,0)      "Orders|OrderID"  false  true
    member val     CustomerID = link  ()   with get     //"Orders|CustomerID"  "Customers|CustomerID"  true
    member val     EmployeeID = link  ()   with get     //"Orders|EmployeeID"  "Employees|EmployeeID"  true
    member val      OrderDate = date  ()   with get     //"Orders|OrderDate" 
    member val   RequiredDate = date  ()   with get     //"Orders|RequiredDate" 
    member val    ShippedDate = date  ()   with get     //"Orders|ShippedDate" 
    member val        ShipVia = link  ()   with get     //"Orders|ShipVia"  "Shippers|ShipperID"  true
    member val        Freight = decimal () with get       // (19,4)  "Orders|Freight"  true
    member val       ShipName = varchar 40 with get
    member val    ShipAddress = varchar 60 with get
    member val       ShipCity = varchar 15 with get
    member val     ShipRegion = varchar 15 with get
    member val ShipPostalCode = varchar 10 with get
    member val    ShipCountry = varchar 15 with get

type Region internal () = 
    inherit ExtTable()
    static member ctor with get() = let x = Region() in x.init(); x
    member val        RegionID   = int   ()   with get      // Region.Init.RegionID        <| Int (10,0)      "Region|RegionID"  false  true
    member val RegionDescription = varchar 50 with get      // Region.Init.RegionDescription <| Str 50           "Region|RegionDescription"  false  false

type Territories internal () = 
    inherit ExtTable()
    static member ctor with get() = let x = Territories() in x.init(); x
    member val     TerritoryID       = varchar 20  with get      //Territories.Init.TerritoryID     <| Str 20           "Territories|TerritoryID"  false  true
    member val TerritoryDescription  = varchar 50  with get      //Territories.Init.TerritoryDescription <| Str 50           "Territories|TerritoryDescription"  false  false
    member val        RegionID       = link   ()   with get      //  <| Link   0  "Territories|RegionID"  "Region|RegionID"  false


[<TestClass>]
type UnitTest_DxpCoreText() = 

    let pretty t = t |> trim |> splitRe "[\s\r\n]+" |> " ".join
    
    [<TestMethod>]
    member x.CheckTxt1 () = 
        let u = Categories.ctor
        let q = query [ from u.all ]
        let expect = @"
SELECT Categories.Id AS 'Id',
       Categories.Picture AS 'Picture',
       Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName',
       Categories.CategoryID AS 'CategoryID'
  FROM Categories"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt2 () = 
        let u = Categories.ctor
        Assert.AreEqual("Id", u.Id.FieldName)
        Assert.AreEqual("CategoryName", u.CategoryName.FieldName)
        let q = query [ from u.all
                        except [ u.Description ; u.CategoryName ]
                      ]
        let expect = @"
SELECT Categories.Id AS 'Id', 
       Categories.Picture AS 'Picture',
       Categories.CategoryID AS 'CategoryID'
  FROM Categories"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.Check_fakefield () = 
        let u = Categories.ctor
        let q = query [ select [ (Q.Param 1).Entitle "LVL"; u.CategoryName ; u.Description ] ]
        let expect = @"
SELECT @a1 AS 'LVL',
       Categories.CategoryName AS 'CategoryName',
       Categories.Description AS 'Description'
  FROM Categories"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt3 () = 
        let u = Categories.ctor
        let q = query [ select [ u.CategoryName ; u.Description ] ]
        let expect = @"
SELECT Categories.CategoryName AS 'CategoryName',
       Categories.Description AS 'Description'
  FROM Categories"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckPart1 () = 
        let u = Categories.ctor
        Assert.AreEqual("(Categories.CategoryID = @a-1)", to_s (u.CategoryID ==. 77)) 
        Assert.AreEqual("((Categories.CategoryID & @a-1) = @a-1)" , (u.CategoryID.HasAll 5) |> to_s) 
        Assert.AreEqual("((Categories.CategoryID & @a-1) <> @a-1)", (u.CategoryID.HasAny 9) |> to_s) // in fact there 0


    [<TestMethod>]
    member x.CheckTxt4 () = 
        let u = Categories.ctor
        let q = query [ select [ u.Description ; u.CategoryName ]
                        where (u.CategoryID ==. 100)
                       ]
        let expect = @"
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID = @a1) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt5 () = 
        let u = Categories.ctor
        let q = query [ select [ u.Description ; u.CategoryName ]
                        where (u.CategoryID ==. 5) ]
        let expect = @"
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID = @a1) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt6 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where (u.CategoryID >. 77) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"
QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID > @a1) )
`@a1`=`77`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt7 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where(u.CategoryID <=. 100) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"
QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID <= @a1) )
`@a1`=`100`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt7xx () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where(u.CategoryID <=. u.Id) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"
QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID <= Categories.Id) )
"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt8 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where ((u.CategoryID ==. 1) ||. u.CategoryID.HasAll 7 ||. u.CategoryID.HasAny 9) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"
QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (((Categories.CategoryID = @a1) OR ((Categories.CategoryID & @a2) = @a3)) OR ((Categories.CategoryID & @a4) <> @a5)) )
`@a1`=`1` `@a2`=`7` `@a3`=`7` `@a4`=`9` `@a5`=`0`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt9 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where ((u.CategoryID >=. 1) &&. (u.CategoryID <=. 7)) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( ((Categories.CategoryID >= @a1) AND (Categories.CategoryID <= @a2)) )
`@a1`=`1` `@a2`=`7`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt10 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where (u.CategoryID.In(1, 7)) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( ((Categories.CategoryID >= @a1) AND (Categories.CategoryID <= @a2)) )
`@a1`=`1` `@a2`=`7`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt11 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where (u.CategoryID.In [1; 3; 4; 7; u.CategoryID]) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID IN (@a1, @a2, @a3, @a4, Categories.CategoryID)) )
`@a1`=`1` `@a2`=`3` `@a3`=`4` `@a4`=`7`"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt12 () = 
        let u = Categories.ctor
        let q = cmd_select [ select [ u.Description ; u.CategoryName ]
                             where (u.CategoryID.In [1 ; u.CategoryID]) ]
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID IN (@a1, Categories.CategoryID)) )
`@a1`=`1` "
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt13 () = 
        let u = Categories.ctor
        let q = query [ select [ u.Description ; u.CategoryName ]
                        where (u.CategoryID.In [Q.Param 51; Q.Param 52; Q.Param 53] ) 
                      ]
        let expect = @"
SELECT Categories.Description AS 'Description',
       Categories.CategoryName AS 'CategoryName'
  FROM Categories
 WHERE ( (Categories.CategoryID IN (@a1, @a2, @a3)) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt14 () = 
        let u = Categories.ctor
        let y1 = Q.Param "aaa"
        let y2 = Q.Param 52
        let y3 = Q.Param 1.123
        let m = [ select [ u.Description ; u.CategoryName ]
                  where (u.CategoryID.In [y1; y2; y3]) ]
        y2.Value <- 125
        let q = Model.AsNonParamSQL <| cmd_select m
        let expect = @"
SELECT Categories.Description AS 'Description',
           Categories.CategoryName AS 'CategoryName'
      FROM Categories
     WHERE ( (Categories.CategoryID IN ('aaa' /* x1/3 */, 125 /* x2/3 */, 1.123 /* x3/3 */)) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxtIsNull () = 
        let u = Categories.ctor
        let y1 = Q.Param "aaa"
        let y2 = Q.Param 52
        let y3 = Q.Param 1.123
        let m = [ select [ u.Description ; u.CategoryName ]
                  where (u.CategoryID.IsNull) ]
        y2.Value <- 125
        let q = Model.AsNonParamSQL <| cmd_select m
        let expect = @"
SELECT Categories.Description AS 'Description',
           Categories.CategoryName AS 'CategoryName'
      FROM Categories
     WHERE ( (Categories.CategoryID IS NULL) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt15 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ select [ o.OrderDate ; o.ShipCity ]
                  select [ c.ContactName ; c.Country ]
                  where (c.ContactName ==. "xxx")
                  join o.CustomerID right c.CustomerID [c.CustomerID ==. "1" ; o.OrderID ==. 2]
                ]
        let q = cmd_select m |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT Orders.OrderDate AS 'OrderDate',
       Orders.ShipCity AS 'ShipCity',
       Customers.ContactName AS 'ContactName',
       Customers.Country AS 'Country'
  FROM Orders
        RIGHT JOIN Customers ON (Orders.CustomerID = Customers.CustomerID) AND (Customers.CustomerID = @a1) AND (Orders.OrderID = @a2)
 WHERE ( (Customers.ContactName = @a3) )
`@a1`=`1` `@a2`=`2` `@a3`=`xxx` "
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt16 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ select [ o.OrderDate ; o.ShipCity ]
                  where (o.OrderDate ==. System.DateTime(1977, 1, 22)) // "1-12-1234")
                  select [ c.ContactName ; c.Country ]
                  where (c.ContactName ==. "xxx")
                  join o.CustomerID right c.CustomerID [c.CustomerID ==. "1" ; o.OrderID ==. 2] 
                  ]
        let q = query m 
        let expect = @"
SELECT Orders.OrderDate AS 'OrderDate',
       Orders.ShipCity AS 'ShipCity',
       Customers.ContactName AS 'ContactName',
       Customers.Country AS 'Country'
  FROM Orders
        RIGHT JOIN Customers ON (Orders.CustomerID = Customers.CustomerID) AND (Customers.CustomerID = @a1) AND (Orders.OrderID = @a2)
 WHERE ( (Orders.OrderDate = @a3)
   AND (Customers.ContactName = @a4) )"
        Assert.AreEqual(pretty expect, pretty q)

//  TODO:
//    [<TestMethod>]
//    member x.CheckTxt16_a () = 
//        let r = Region.ctor
//        let t = Territories.ctor
//        let m = [ select [ r.RegionDescription ; t.TerritoryDescription ]
//                  join_table r.all left t.all [t.TerritoryDescription ==. "1"] ]
//        let q = query m 
//        let expect = @"
//SELECT Region.RegionDescription AS 'RegionDescription',
//        Territories.TerritoryDescription AS 'TerritoryDescription'
//  FROM Region
//        LEFT JOIN Territories ON (Region.RegionID = Territories.RegionID) AND (Territories.TerritoryDescription = @a1)  "
//        Assert.AreEqual(pretty expect, pretty q)
//


    [<TestMethod>]
    member x.CheckTxt17 () = 
        let c = Customers.ctor
        let o = Orders.ctor

        let cn = Q.Param "yyy"
        let o' = [ select [ o.OrderDate ;  o.ShipCity ]
                   where (o.OrderDate ==. System.DateTime(1977, 1, 22)) ]
        let c' = [ select [ c.ContactName ; c.Country ]
                   where (c.ContactName ==. cn) ]
        let m = o' @ c' @ [ join o.CustomerID left c.CustomerID [c.CustomerID ==. "1"; o.OrderID ==. 2] ]
        cn.Value <- "cname"
        
        let q = Model.AsNonParamSQL <| cmd_select m
        let expect = @"
SELECT Orders.OrderDate AS 'OrderDate',
       Orders.ShipCity AS 'ShipCity',
       Customers.ContactName AS 'ContactName',
       Customers.Country AS 'Country'
  FROM Orders
        LEFT JOIN Customers ON (Orders.CustomerID = Customers.CustomerID) AND (Customers.CustomerID = '1' /* x1/4 */) AND (Orders.OrderID = 2 /* x2/4 */)
 WHERE ( (Orders.OrderDate = 1977-01-22 00:00:00 /* x3/4 */)
   AND (Customers.ContactName = 'cname' /* x4/4 */) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.Check_update () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ update [ o.OrderDate.Set 4 ]
                  where (o.OrderDate ==. System.DateTime(1977, 1, 22))
                  where (o.OrderID ==. 8) ]
        let q = Model.AsNonParamSQL <| cmd_update m
        let expect = @"
UPDATE Orders SET OrderDate = 4 /* x1/3 */
 WHERE ( (Orders.OrderDate = 1977-01-22 00:00:00 /* x2/3 */)
   AND (Orders.OrderID = 8 /* x3/3 */) )"
        Assert.AreEqual(pretty expect, pretty q)

    [<TestMethod>]
    member x.Check_updatebit () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let v = 4
        let m = [ update [ o.EmployeeID.Set (o.EmployeeID &~. v) ]
                  where (o.OrderDate ==. System.DateTime(1977, 1, 22))
                  where (o.OrderID ==. 8) ]
        let q = Model.AsNonParamSQL <| cmd_update m
        let expect = @"
UPDATE Orders SET EmployeeID = (Orders.EmployeeID & ~4 /* x1/3 */)
 WHERE ( (Orders.OrderDate = 1977-01-22 00:00:00 /* x2/3 */)
   AND (Orders.OrderID = 8 /* x3/3 */) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt19 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ update [ o.OrderDate.Set 4 ; o.OrderID.Set 8 ; o.Freight.Set "-" ]
                  where ((o.OrderDate ==. System.DateTime(1977, 1, 22)) &&. (o.OrderID ==. 8)) ]
        let q = Model.AsNonParamSQL <| cmd_update m
        let expect = @"
UPDATE Orders SET OrderDate = 4 /* x1/5 */, OrderID = 8 /* x2/5 */, Freight = '-' /* x3/5 */
 WHERE ( ((Orders.OrderDate = 1977-01-22 00:00:00 /* x4/5 */) AND (Orders.OrderID = 8 /* x5/5 */)) )"
        Assert.AreEqual(pretty expect, pretty q)

    [<TestMethod>]
    member x.CheckTxt20 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ update [ o.OrderDate.Set 4 ; o.OrderID.Set 8 ; o.Freight.Set "-" ]
                  where ((o.OrderDate ==. System.DateTime(1977, 1, 22)) &&. (o.OrderID ==. 8)) ]
        let q = Model.AsNonParamSQL <| cmd_insert m
        let expect = @"INSERT INTO Orders(OrderDate, OrderID, Freight)
 VALUES (4 /* x1/5 */, 8 /* x2/5 */, '-' /* x3/5 */)"
        Assert.AreEqual(pretty expect, pretty q)

    [<TestMethod>]
    member x.CheckTxt21 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let m = [ update [ o.OrderDate.Set 4 ; o.OrderID.Set 8 ; o.Freight.Set "-" ]
                  where ((o.OrderDate ==. System.DateTime(1977, 1, 22)) &&. (o.OrderID ==. 8)) ]
        let q = Model.AsNonParamSQL <| cmd_delete m
        let expect = @"DELETE FROM Orders 
 WHERE ( ((Orders.OrderDate = 1977-01-22 00:00:00 /* x4/5 */) AND (Orders.OrderID = 8 /* x5/5 */)) )"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt22 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate.Min ; o.OrderID ; o.Freight ]
                             where (o.OrderID >=. 100) ] |> Debug.SqlCommand.asLogInfo 
        let expect = @"QRY
SELECT MIN(Orders.OrderDate) AS 'OrderDate',
       Orders.OrderID AS 'OrderID',
       Orders.Freight AS 'Freight'
  FROM Orders
 WHERE ( (Orders.OrderID >= @a1) )
 GROUP BY Orders.OrderID, Orders.Freight
`@a1`=`100` "
        Assert.AreEqual(pretty expect, pretty q)

    [<TestMethod>]
    member x.CheckTxt22xx () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate.Min ]
                             where (o.OrderID >=. 100) ] |> Debug.SqlCommand.asLogInfo 
        let expect = @"QRY
SELECT MIN(Orders.OrderDate) AS 'OrderDate'
  FROM Orders
 WHERE ( (Orders.OrderID >= @a1) )
`@a1`=`100` "
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt100 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate ; o.OrderID ; o.Freight ]
                             take 2
                             skip 4
                             order_by [ o.OrderDate ]
                           ] 
                |> Debug.SqlCommand.asLogInfo 
        let expect = @"QRY
SELECT xxx0 AS 'OrderDate',
       xxx1 AS 'OrderID',
       xxx2 AS 'Freight'FROM (SELECT Orders.OrderDate AS xxx0,
       Orders.OrderID AS xxx1,
       Orders.Freight AS xxx2, ROW_NUMBER() OVER (ORDER BY Orders.OrderDate) AS row_enumerator
  FROM Orders ) AS MyDerivedTable WHERE MyDerivedTable.row_enumerator BETWEEN 5 AND 6"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt101 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate ; o.OrderID ; o.Freight ]
                             take 2
                             skip 4
                           ] 
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT xxx0 AS 'OrderDate',
       xxx1 AS 'OrderID',
       xxx2 AS 'Freight'FROM (SELECT Orders.OrderDate AS xxx0,
       Orders.OrderID AS xxx1,
       Orders.Freight AS xxx2, ROW_NUMBER() OVER (ORDER BY ID) AS row_enumerator
  FROM Orders ) AS MyDerivedTable WHERE MyDerivedTable.row_enumerator BETWEEN 5 AND 6"
        Assert.AreEqual(pretty expect, pretty q)



    [<TestMethod>]
    member x.CheckTxt201 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate ; o.OrderID ; o.Freight ]
                             order_by [ o.Freight ]
                             take 2
                             skip 4
                           ] 
                |> Debug.SqlCommand.asLogInfo
        let expect = @"QRY
SELECT xxx0 AS 'OrderDate',
       xxx1 AS 'OrderID',
       xxx2 AS 'Freight'FROM (SELECT Orders.OrderDate AS xxx0,
       Orders.OrderID AS xxx1,
       Orders.Freight AS xxx2, ROW_NUMBER() OVER (ORDER BY Orders.Freight) AS row_enumerator
  FROM Orders ) AS MyDerivedTable WHERE MyDerivedTable.row_enumerator BETWEEN 5 AND 6"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt102 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate ; o.OrderID ; o.Freight ]
                             skip 4
                           ] 
                |> Debug.SqlCommand.asLogInfo 
        let expect = @"QRY
SELECT xxx0 AS 'OrderDate',
       xxx1 AS 'OrderID',
       xxx2 AS 'Freight'FROM (SELECT Orders.OrderDate AS xxx0,
       Orders.OrderID AS xxx1,
       Orders.Freight AS xxx2, ROW_NUMBER() OVER (ORDER BY ID) AS row_enumerator
  FROM Orders ) AS MyDerivedTable WHERE MyDerivedTable.row_enumerator >= 5"
        Assert.AreEqual(pretty expect, pretty q)


    [<TestMethod>]
    member x.CheckTxt110 () = 
        let c = Customers.ctor
        let o = Orders.ctor
        let q = cmd_select [ select [ o.OrderDate ; o.OrderID ; o.Freight ]
                             take 2
                             order_by [ o.OrderDate ]
                           ] 
                |> Debug.SqlCommand.asLogInfo 
        let expect = @"QRY
SELECT TOP 2 Orders.OrderDate AS 'OrderDate',
       Orders.OrderID AS 'OrderID',
       Orders.Freight AS 'Freight'
  FROM Orders
ORDER BY Orders.OrderDate"
        Assert.AreEqual(pretty expect, pretty q)

